package com.jzxk.tool;

import org.apache.commons.io.FileUtils;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.Cell;

import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.math.BigDecimal;
import java.net.URLEncoder;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;

/**
 * @author 今朝惜客
 * @CreateTime : 16/9/9 上午8:26
 * @Description : Excel工具类
 */
public class ExcelUtils {

	/**
	 * 导入文件
	 *
	 * @param filePath 文件路径
	 * @return
	 */
	public static List<Object> importExcel(String filePath) {

		return importExcel(filePath, 0);
	}

	/**
	 * 导入文件
	 *
	 * @param filePath 文件路径
	 * @param beginRow 起始行数,默认为零
	 * @return
	 */
	public static List<Object> importExcel(String filePath, int beginRow) {

		List<Object> datas = new ArrayList<Object>();

		try {

			File file = new File(filePath);
			HSSFWorkbook workbook = new HSSFWorkbook(FileUtils.openInputStream(file));

			for (int numSheet = 0; numSheet < workbook.getNumberOfSheets(); numSheet++) {

				HSSFSheet sheet = workbook.getSheetAt(numSheet);

				if (sheet == null)
					continue;

				if (beginRow > sheet.getLastRowNum())
					throw new RuntimeException("起始行数大于最大行数");
				//循环行Row
				for (int numRow = beginRow; numRow <= sheet.getLastRowNum(); numRow++) {

					HSSFRow row = sheet.getRow(numRow);

					if (row == null)
						continue;

					//循环列Cell
					for (int numCell = 0; numCell < row.getLastCellNum(); numCell++) {
						Cell cell = row.getCell(numCell);
						if (cell != null) {
							datas.add(getCellValue(cell));
						}
					}
				}
			}

		} catch (Exception e) {
			e.printStackTrace();
		}

		return datas;
	}

	/**
	 * 导出Excel文件,本地目录
	 *
	 * @param sheetName
	 * @param titles
	 * @param datas
	 * @param exportFilePath
	 */
	public static void exportExcelToLocal(String sheetName, String[] titles, List<List<Object>> datas, String exportFilePath) {

		if (StringUtils.isBlank(exportFilePath)) {
			throw new RuntimeException("请输入导出路径");
		}

		HSSFWorkbook workBook = exportExcel(sheetName, titles, datas);
		if (workBook != null) {

			File file = new File(exportFilePath);

			if (!file.exists()) {

				try {
					file.createNewFile();
				} catch (IOException e) {
					e.printStackTrace();
				}
			}

			try {
				FileOutputStream fileOutPutStream = new FileOutputStream(file);

				workBook.write(fileOutPutStream);
				fileOutPutStream.flush();
				fileOutPutStream.close();

			} catch (FileNotFoundException e) {
				e.printStackTrace();
			} catch (IOException e) {
				e.printStackTrace();
			}
		}
	}

	/**
	 * 导出Excel文件,web请求
	 * @param fileName
	 * @param sheetName
	 * @param titles
	 * @param datas
	 * @param response
	 */
	public static void exportExcelToWeb(String fileName, String sheetName, String[] titles, List<List<Object>> datas,
			HttpServletResponse response) {

		if (StringUtils.isBlank(fileName)) {
			throw new RuntimeException("请输入文件名称");
		}

		HSSFWorkbook workBook = exportExcel(sheetName, titles, datas);

		try {

			String now = DateUtils.format(Calendar.getInstance().getTime(), DateUtils.TIMESTAMP);
			response.reset();
			response.setContentType("application/vnd.ms-excel;charset=gbk");
			response.setHeader("Content-disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8") + now + ".xls");
			response.setCharacterEncoding("UTF-8");
			OutputStream ouputStream = response.getOutputStream();
			workBook.write(ouputStream);
			ouputStream.flush();
			ouputStream.close();

		} catch (Exception e) {
			e.printStackTrace();
		}

	}

	/**
	 * 导出Excel文件
	 *
	 * @param sheetName
	 * @param titles
	 * @param datas
	 */
	public static HSSFWorkbook exportExcel(String sheetName, String[] titles, List<List<Object>> datas) {

		if (titles.length > 0 && datas != null && datas.size() > 0) {
			if (StringUtils.isBlank(sheetName))
				sheetName = "Sheel1";

			//声明一个工作薄
			HSSFWorkbook workBook = new HSSFWorkbook();

			//生成一个表格
			HSSFSheet sheet = workBook.createSheet(sheetName);

			//设置行样式
			HSSFCellStyle cellStyle = workBook.createCellStyle();

			//水平居中
			cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);

			//垂直
			cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);

			//自动换行
			cellStyle.setWrapText(true);

			//创建标题行
			HSSFRow row = sheet.createRow(0);

			//遍历titles
			for (int numCell = 0; numCell < titles.length; numCell++) {
				HSSFCell cell = row.createCell(numCell);
				HSSFRichTextString text = new HSSFRichTextString(titles[numCell]);
				cell.setCellValue(text);
				cell.setCellStyle(cellStyle);
				sheet.setColumnWidth(numCell, 4500);
			}

			for (int i = 0; i < datas.size(); i++) {

				row = sheet.createRow(i + 1);
				//行高
				row.setHeight((short) 500);

				for (int iCol = 0; iCol < datas.get(i).size(); iCol++) {
					HSSFCell cell = row.createCell(iCol);
					cell.setCellStyle(cellStyle);

					Object value = datas.get(i).get(iCol);
					String textValue;
					HSSFRichTextString richText;

					if (value instanceof Date) {

						textValue = DateUtils.format((Date) value, DateUtils.TIME_STAMP);
						richText = new HSSFRichTextString(textValue);
						cell.setCellValue(richText);
					} else if (value instanceof BigDecimal) {
						DecimalFormat df = new DecimalFormat("0.00");
						textValue = df.format(value);
						richText = new HSSFRichTextString(blank2Empty(textValue));
						cell.setCellValue(richText);
					} else if (value instanceof Boolean) {
						if ((Boolean) value) {
							richText = new HSSFRichTextString(new String("是"));
						} else {
							richText = new HSSFRichTextString(new String("否"));
						}
						cell.setCellValue(richText);
					} else {
						textValue = value.toString();
						richText = new HSSFRichTextString(blank2Empty(textValue));
						cell.setCellValue(richText);
					}
				}
			}

			return workBook;
		} else
			return null;
	}

	/**
	 * 转换行文本
	 *
	 * @param cell
	 * @return
	 */
	private static String getCellValue(Cell cell) {

		String cellValue;
		if (cell == null) {
			return "";
		}

		switch (cell.getCellType()) {
		case HSSFCell.CELL_TYPE_STRING:
			cellValue = cell.getStringCellValue();
			break;
		case HSSFCell.CELL_TYPE_NUMERIC:
			DecimalFormat df = new DecimalFormat("0.##");
			cellValue = df.format(cell.getNumericCellValue());
			break;
		case HSSFCell.CELL_TYPE_BOOLEAN:
			cellValue = String.valueOf(cell.getBooleanCellValue());
			break;
		default:
			cellValue = "";
			break;
		}

		if (cellValue.equals("") || cellValue == null)
			return "";

		return cellValue;
	}

	private static String blank2Empty(String str) {
		if (StringUtils.isBlank(str)) {
			return "";
		} else {
			return str;
		}

	}
}
